package com.easyidea.its.domain.repository.tracklog.impl;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.easyidea.its.domain.model.passport.TrackLog;
import com.easyidea.its.domain.repository.base.impl.BaseRepositoryImpl;
import com.easyidea.its.domain.repository.tracklog.TrackLogRepository;
import com.easyidea.its.util.DateUtil;
import com.easyidea.its.util.ItsConstants;
import com.easyidea.its.util.ParamsUtilities;
import com.easyidea.its.web.dto.CarSeizureDto;
import com.easyidea.its.web.dto.StatisticsDto;

public class TrackLogRepositoryImpl extends BaseRepositoryImpl<TrackLog>
		implements TrackLogRepository {

	@SuppressWarnings("unchecked")
	public TrackLog getLatestTrackLogByCondition(String passPortName,
			String direction, boolean searchLeastRecord, Integer tracklogId, Date passTime) {
		
		Criteria ca = createUndeleteCriteria();
		ca.createCriteria("passPort", "passPort", CriteriaSpecification.INNER_JOIN);
		if(tracklogId != null) {
			ca.add(Restrictions.eq("id", tracklogId));
		}
		if (passPortName != null && !passPortName.equals("")) {
			ca.add(Restrictions.eq("passPort.passPortName", passPortName));
		}
		if (direction != null && !direction.equals("")) {
			ca.add(Restrictions.eq("direction", direction));
		}
		if (passTime != null ) {
			ca.add(Restrictions.eq("passTime", passTime));
		}		
		if (searchLeastRecord) {
			ca.addOrder(Order.desc("passTime"));
		}
		ca.setMaxResults(1);
		ca.setFetchMode("passPort", FetchMode.SELECT);
		List<TrackLog> tls = (List<TrackLog>) ca.list();
		if (tls != null && tls.size() > 0) {
			return (TrackLog) tls.get(0);
		}
		return null;
	}

	public List<TrackLog> getTrackLogsByCondition(CarSeizureDto carSeizureDto,
			int startAtIndex, int pageSize) {
		Criteria ca = this.getTrackLogsByConditionCriteria(carSeizureDto);
		if (startAtIndex > 0) {
			ca.setFirstResult(startAtIndex);
		}
		if (pageSize > 0) {
			ca.setMaxResults(pageSize);
		}
		return ca.list();
	}



	private Criteria getTrackLogsByConditionCriteria(CarSeizureDto carSeizureDto){
		Criteria ca = this.createUndeleteCriteria();
		ca.createAlias("passPort", "passPort");
		ca.createAlias("passPort.department", "department");
		if(carSeizureDto.getSearchType() != null&&carSeizureDto.getSearchType().equals("precision")){
			if(carSeizureDto.getPlateNumber() != null) {
				ca.add(Restrictions.eq("plateNumber", carSeizureDto.getPlateNumber()));
			}
		}else if(carSeizureDto.getSearchType() != null&&carSeizureDto.getSearchType().equals("fuzzy"))
		{
			if(carSeizureDto.getPlateNumber() != null&&carSeizureDto.getPlateNumber().length()>0) {
			String plateNumber = carSeizureDto.getPlateNumber();
			final StringBuffer finalStr = new StringBuffer();
			for (int i = 0; i < plateNumber.length(); i++) {
				finalStr.append(ParamsUtilities.convertChar(plateNumber.charAt(i)));
			}
			ca.add(Restrictions.like("plateNumber", finalStr.toString()));
			}
		}
		
			

		if(carSeizureDto.getPlateType() != null && !carSeizureDto.getPlateType().equals("all")) {
			ca.add(Restrictions.eq("plateType", carSeizureDto.getPlateType()));
		}		
		if (carSeizureDto.getLaneName() != null && !carSeizureDto.getLaneName().equals("all")) {
			ca.add(Restrictions.eq("laneName", carSeizureDto.getLaneName()));
		}
		if(carSeizureDto.getPassportName() != null && !carSeizureDto.getPassportName().equals("all")){
			ca.add(Restrictions.eq("passPort.passPortName", carSeizureDto.getPassportName()));
		}
		if(carSeizureDto.getDirection() != null && !carSeizureDto.getDirection().equals("all")){
			ca.add(Restrictions.eq("direction", carSeizureDto.getDirection()));
		}		
		if(carSeizureDto.getDepartmentId() != null && carSeizureDto.getDepartmentId() != -1){
			ca.add(Restrictions.eq("department.id", carSeizureDto.getDepartmentId()));
		}
		if (carSeizureDto.getStartSpeed() != null && carSeizureDto.getEndSpeed() != null) {
			ca.add(Restrictions.between("speed", carSeizureDto.getStartSpeed(), carSeizureDto.getEndSpeed()));
		}
		if (carSeizureDto.getStartDate() != null && carSeizureDto.getEndDate() != null) {
			String sPassTime = DateUtil.dateToLongStr(carSeizureDto.getStartDate(), carSeizureDto.getStartHour(), carSeizureDto.getStartMinute());
			Date startPassTime = DateUtil.strToDate(sPassTime, ItsConstants.FORMATE_STYLE_DATA_NO_SECOND);
			String ePassTime = DateUtil.dateToLongStr(carSeizureDto.getEndDate(), carSeizureDto.getEndHour(), carSeizureDto.getEndMinute());
			Date endPassTime = DateUtil.strToDate(ePassTime, ItsConstants.FORMATE_STYLE_DATA_NO_SECOND);
			ca.add(Restrictions.between("passTime", startPassTime, endPassTime));
		}else {
			if(carSeizureDto.getTodayOrHistory() == 0) {
				ca.add(Restrictions.between("passTime", DateUtil.getTodayStart(), DateUtil.getTodayEnd()));
			} else {
				ca.add(Restrictions.le("passTime", DateUtil.getEndTime(DateUtil.getYestoday())));
			}
		}
		ca.addOrder(Order.desc("passTime"));
		return ca;
	}

	public int getTrackLogsCountByCondition(CarSeizureDto carSeizureDto) {
		Criteria ca = this.getTrackLogsByConditionCriteria(carSeizureDto);
		ca.setProjection(Projections.count("id"));
		return (Integer) ca.uniqueResult();
	}
	public Collection<Object[]> statisticsByCondition(StatisticsDto statisticsDto) {
		String field = "";
		String groupBy = "";
		if(statisticsDto.getStatisticsType().equals("hour")) {
			field = " hour(this_.passTime) " ;
			groupBy = " group by hour(this_.passTime)";
		} else if(statisticsDto.getStatisticsType().equals("day")) {
			field = " date(this_.passTime) " ;
			groupBy = " group by date(this_.passTime)";
		} else if(statisticsDto.getStatisticsType().equals("month")) {
			field = " month(this_.passTime) " ;
			groupBy = " group by month(this_.passTime)";
		}		
		String  sql = " select count(this_.id) as num, " + field 
				+ " from TrackLog this_ inner join PassPort passport1_ on this_.passPortName=passport1_.passPortName" +
				" inner join Department department2_ on passport1_.department_id=department2_.id ";
	   String where = "  where this_.is_deleted=0 ";
	    	if(statisticsDto.getDepartmentId() != null && statisticsDto.getDepartmentId() != -1) {
	    		where += " and department2_.id = " + statisticsDto.getDepartmentId(); 
	    	}
	    	if(statisticsDto.getPassportName() != null && !statisticsDto.getPassportName().equals("all")) {
	    		where += " and passport1_.passPortName = '" + statisticsDto.getPassportName() +"'"; 
	    	}
	    	if(statisticsDto.getDirection() != null && !statisticsDto.getDirection().equals("all")) {
	    		where += " and passport1_.passPortName = '" + statisticsDto.getDirection() +"'"; 
	    	}	
	    	if(statisticsDto.getDirection() != null && !statisticsDto.getDirection().equals("all")) {
	    		where += " and passport1_.passPortName = '" + statisticsDto.getDirection() +"'"; 
	    	}	
	    	if(statisticsDto.getLaneName() != null && !statisticsDto.getLaneName().equals("all")) {
	    		where += " and this_.laneName = '" + statisticsDto.getLaneName() +"'"; 
	    	}	
	    	if (statisticsDto.getStartDate() != null && statisticsDto.getEndDate() != null) {
	    		where += " and this_.passTime >= '" + DateUtil.dateToLongStr(statisticsDto.getStartDate()) +"' and this_.passTime <= '" + DateUtil.dateToLongStr(statisticsDto.getEndDate()) +"'"; 
	    	}

		sql = sql + where + groupBy;
		
		return this.executePureSqlCol(sql);
	}
}
